<?php
    class Admincp_Model_Tag extends Zend_Db_Table_Abstract
    {
        protected $_db;
		
		public function __construct()
		{
			$this->_db = Zend_Registry::get('db');
		}
        
        public function listTag($locale)
        {
            $sql = $this->_db->select()
                            ->from('tag AS t')
                            ->join('tag_language AS tl', 't.id = tl.tag_id', array('name'))
                            ->join('language AS l', 'tl.language_id = l.id', null)
                            ->where('l.locale = ?', $locale)
                            ->order('t.id DESC');
            return $sql;
        }
        
        public function listTagSelectbox()
        {
            $sql = $this->_db->select()
                            ->from('tag AS t', array('id'))
                            ->join('tag_language AS tl', 't.id = tl.tag_id', array('name'))
                            ->join('language AS l', 'tl.language_id = l.id', null)
                            ->where('l.locale = ?', 'vi')
                            ->order('t.id DESC');
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function getTagById($id)
        {
            $sql = $this->_db->select()
                            ->from('tag_language AS tl', array('name'))
                            ->join('language AS l', 'tl.language_id = l.id', array('locale'))
                            ->where('tl.tag_id = ?', $id);
            $result = $this->_db->fetchAll($sql);
            return $result;
        }
        
        public function getTagNameById($id)
        {
            $sql = $this->_db->select()
                            ->from('tag_language AS tl', array('name'))
                            ->join('language AS l', 'tl.language_id = l.id', null)
                            ->where('tl.tag_id = ?', $id)
                            ->where('l.locale = ?', 'vi');
            $result = $this->_db->fetchOne($sql);
            return $result;
        }
        
        public function checkTag($name, $id = null)
        {
            $sql = $this->_db->select()
                            ->from('tag_language')
                            ->where('name = ?', $name);
            if($id != null) {
                $sql = $sql->where('tag_id <> ?', $id);
            }
            $result = $this->_db->fetchAll($sql);
            return count($result);
        }
        
        public function addTag($data)
        {
            $this->_db->insert('tag',$data);
            return $this->_db->lastInsertId();
        }
        
        public function addTagLanguage($data)
        {
            $this->_db->insert('tag_language',$data);
        }
        
        public function deleteTag($id)
        {
            $where = $this->getAdapter()->quoteInto('id = ?', $id);
            $this->_db->delete('tag', $where);
            $where = $this->getAdapter()->quoteInto('tag_id = ?', $id);
            $this->_db->delete('tag_language', $where);
        }
        
        public function deleteMultiTag($listId)
        {
            foreach($listId as $id) {
                $this->deleteTag($id);
            }
        }
        
        public function updateTag($data, $id)
        {
            $where = $this->getAdapter()->quoteInto('id = ?', $id);
            $this->_db->update('tag', $data, $where);
        }
        
        public function updateTagLanguage($data,$id)
        {
            $where = 'tag_id = '.$id.' AND language_id = '.$data['language_id'];
            $this->_db->update('tag_language', $data, $where);
        }
        
        public function searchTag($data, $locale)
        {
            $sql = $this->_db->select()
		                    ->from('tag AS t')
                            ->join('tag_language AS tl', 't.id = tl.tag_id', array('name'))
                            ->join('language AS l', 'tl.language_id = l.id', null)
                            ->where('l.locale = ?', $locale);
		    if($data['name'] != '') {
		        $sql = $sql->where('tl.name_plaintext LIKE ?', $data['name']);
		    }
		    if($data['status'] != 0) {
		        $sql = $sql->where('t.status = ?', $data['status']);
		    }
		    return $sql;
        }
    }
